﻿<%@ Page 
Language="C#" 
Buffer="false"
%>

<%@ Import Namespace="System.Text" %>
<%@ Import Namespace="System.Xml" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Net" %>
<%@ Import Namespace="System.IO" %>

<script type="text/c#" language="c#" runat="server">

string ConnStr = GetAppSetting("DB04Connect");
DateTime dt = DateTime.Now;

protected override void OnPreInit(EventArgs e){
    // Enabled trace in code
    this.Context.Trace.IsEnabled = false;
}

protected void Page_Load(object sender, EventArgs e){

    dt = DateTime.Now;
    if (Request.Params.Count > 0)
    {
        string str = Request.Params["dt"];
        if (str != null)
            dt = DateTime.Parse(str);
    }

	XmlDocument xReturn = getXML();
	Response.ContentType = "text/xml";
	Response.Write(xReturn.OuterXml);
}

public XmlDocument getXML()
{
    XmlDocument xReturn = new XmlDocument();

    DataTable tbl = new DataTable("tbl");
    Exception err = GetTbl(ref tbl, ConnStr, dt);

    tbl.Columns[0].ColumnName = "ShopName";
    tbl.Columns[0].Caption = "ShopName";
    tbl.Columns[1].ColumnName = "Till1";
    tbl.Columns[1].Caption = "Till1";
    tbl.Columns[2].ColumnName = "Till2";
    tbl.Columns[2].Caption = "Till2";
    tbl.Columns[3].ColumnName = "Till3";
    tbl.Columns[3].Caption = "Till3";
    tbl.Columns[4].ColumnName = "Till4";
    tbl.Columns[4].Caption = "Till4";
    tbl.Columns[5].ColumnName = "Total";
    tbl.Columns[5].Caption = "Total";

    StringWriter stringWriter = new StringWriter();
    XmlTextWriter xmlTextwriter = new XmlTextWriter(stringWriter);
    tbl.WriteXml(xmlTextwriter, XmlWriteMode.WriteSchema);
    //XmlNode xRoot = xReturn.CreateElement("root");
    //xReturn.AppendChild(xRoot);
    xReturn.LoadXml(stringWriter.ToString());

    return xReturn;
}

Exception GetTbl(ref DataTable tbl, string parConnStr, DateTime parDate)
{
    Exception ret = null;
    if (tbl == null)
        tbl = new DataTable();

    try
    {
        using (OleDbConnection cn = new OleDbConnection(parConnStr.Replace("DB_Ex", "DBMySQLTill")))
        {
            using (OleDbCommand myCMD = new OleDbCommand("Till_MYSQL_get_tills_data", cn))
            {
                myCMD.CommandType = CommandType.StoredProcedure;

                OleDbParameter date = myCMD.Parameters.Add("@dateStart", OleDbType.VarChar, 20);
                date.Direction = ParameterDirection.Input;

                OleDbParameter server = myCMD.Parameters.Add("@server", OleDbType.VarChar, 100);
                server.Direction = ParameterDirection.Input;

                date.Value = parDate.ToString("yyyy-MM-dd");
                server.Value = "DBMYSQLTILL_ALLSHOPS";
                cn.Open();

                using (OleDbDataAdapter adapter = new OleDbDataAdapter(myCMD))
                {
                    adapter.Fill(tbl);
                }
            }
            cn.Close();
        }
    }
    catch (Exception ex)
    {
        ret = new Exception(ex.Message, ex);
    }

    return ret;
}

static public string GetAppSetting(String mName)
{
    String vStr = null;
    string strPath =
@"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
    XmlDocument xmlDoc = new XmlDocument();
    xmlDoc.Load(strPath);
    XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
    foreach (XmlNode keynode in node.ChildNodes)
    {
        string keyStr = keynode.Attributes["key"].Value;
        if (keyStr == mName)
        {
            vStr = keynode.Attributes["value"].Value;
            break;
        }
    }

    if (String.IsNullOrEmpty(vStr))
        vStr = "";

    return vStr;
}

</script>

